package cn.utility;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import com.fasterxml.jackson.databind.type.CollectionLikeType;

import cn.lnexin.db.DataBaseUtil;
import cn.lnexin.format.JsonUtil;

public class ProvinceDeal {
	public static void main(String[] args) throws SQLException {
		DataBaseUtil jdbc = new DataBaseUtil();
		jdbc.getConn();
		List<Map> selectMore = jdbc.selectMore("select * from province;", null);
		List<Province> provs = new ArrayList<>();
		for (Map map : selectMore) {
			Province p = getProvinces(map);
			
			List<Map> cityResults = jdbc.selectMore("select * from city where pid=" + p.getId(), null);
			List<City> citys = new ArrayList<>();
			for (Map city : cityResults) {
				City c = new City();
				c.setCid(Integer.valueOf(city.get("cid").toString()));
				c.setName(String.valueOf(city.get("name")));
				c.setPid(Integer.valueOf(city.get("pid").toString()));
				if (c.getCid() == 1) {
					p.setCapital(c);
				}
				
				citys.add(c);
			}
			p.setCitys(citys);
			List<Province> adjoins = getADJoinProv(jdbc, p.getId());
			p.setAdjoins(adjoins);
			
			provs.add(p);
		}

		String string = JsonUtil.objToJSONString(provs);
		System.out.println(string);
		for (Province province : provs) {
			System.out.println(province);
		}
		System.out.println("====================================================================================");
		provs = provs.stream().sorted((x,y)->{
			return x.getAdjoins().size()>y.getAdjoins().size() ? 1:-1;
		}).collect(Collectors.toList());
		
		for (Province province : provs) {
			System.out.println(province);
		}
	}
	
	static Province getProvinces(Map province) throws SQLException {
		Province p = new Province();
		p.setId(Integer.valueOf(province.get("id").toString()));
		p.setName(String.valueOf(province.get("name")));
		p.setEnName(String.valueOf(province.get("en_name")));
		p.setShortName(String.valueOf(province.get("short_name")));
		p.setEnShortName(String.valueOf(province.get("en_short_name")));
		p.setArea(String.valueOf(province.get("area")));
		p.setCode(Integer.valueOf(province.get("code").toString()));
		return p;
		
	}
	
	private static List<Province> getADJoinProv(DataBaseUtil jdbc, Integer id) throws SQLException {
		StringBuilder ins = new StringBuilder();
		int i = 0;
		Map<String, Integer> provResult = jdbc.selectOne("select * from adjoin where  id =" + id, null);
		for (Map.Entry<String, Integer> en : provResult.entrySet()) {
			if (!en.getKey().equals("id") && en.getValue() == 1){
				if (i > 0) ins.append(",");
				ins.append(en.getKey());
				i++;
			}
		}
		if (ins.length()>0) {
			
		String sql = "select * from province where id in (" + ins + ");";
//		System.out.println(sql);
		List<Map> results = jdbc.selectMore(sql, null);
		
		List<Province> returns = new ArrayList<>();
		for (Map r : results)
			returns.add(getProvinces(r));
		return returns;
		}else {
			return new ArrayList<>();
		}
	}
}
